package com.league.util;


import com.bafa.dbcache.GrabDivision;
import com.bafa.dbcache.GrabTeamStats;
import com.bafa.dbcache.GrabTeams;
import com.bafa.dbcache.LeagueCache;
import com.bafa.dbcache.RetrieveCache;
import com.bafa.jdbc.DBAccess;
import com.bafa.jdbc.QueryObject;
import com.bafa.middle.betwixt.Utils;
import com.bafa.objects.player.Player;
import com.bafa.objects.player.PlayerDivision;
import com.bafa.objects.player.PlayerMatch;
import com.bafa.objects.player.PlayerSeason;
import com.bafa.objects.player.PlayerTeam;
import com.bafa.objects.schema.Division;
import com.bafa.objects.schema.Match;

import com.bafa.objects.schema.ObjectFactory;
import com.bafa.objects.schema.Round;
import com.bafa.objects.schema.Score;
import com.bafa.objects.schema.Scorer;

import com.bafa.objects.schema.Season;

import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

import com.bafa.objects.team.Team;
import com.bafa.objects.schema.TeamDetails;
import com.bafa.objects.schema.TeamSeason;

import com.bafa.objects.team.TeamStats;
import com.bafa.services.RetrieveServices;
import com.bafa.services.SeasonServices;

import java.io.ByteArrayInputStream;

import java.sql.Date;

import java.util.HashMap;
import java.util.Iterator;
import java.util.TreeMap;

public class ReverseEngineerData {

	private Connection conn = null;
	public void disconnect(){
		try
		{
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
		private Connection connect(){
			try {
				Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			}

			catch (Exception E) {
				System.err.println("Unable to load driver.");
				E.printStackTrace();
			}
			
			try{
				conn = DriverManager.getConnection("jdbc:mysql://localhost/footydb?user=footy&password=rdr");
				//conn.setAutoCommit(false);
			}catch(Exception e){
				e.printStackTrace();
			}

			return conn;

		}
		/*
		public void reverseleague()throws Exception{
			connect();
			PreparedStatement updateTeam = conn.prepareStatement("select * from teamblobs");
			int j = 1;
		    ResultSet teamsResult = updateTeam.executeQuery();
		    while(teamsResult.next()){
		             try{
		                     String string = new String(teamsResult.getBytes("team"));
		                 
		                 JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
		                    StringReader reader = new StringReader(string);
		                    Unmarshaller marshaller=jaxbContext.createUnmarshaller();
		                    
		                    Team team = (Team)marshaller.unmarshal(reader);
		                 
		                 
		            
			            
						List seasons = team.getTeamStats().getTeamSeason();
						PreparedStatement updateLeague = conn.prepareStatement("insert into league values(?,?,?,?,?,?,?,?,?,?,?,?)");
						for (int i=0;i<seasons.size();i++){
							
                                                        TeamSeason season = (TeamSeason)seasons.get(i);
							if(season.getDivision()>0){
                                                        int leaguedrawn = season.getNumberOfLeagueGamesDrawn();
							int leaguewon = season.getNumberOfLeagueGamesDrawn();
							int leaguelost = season.getNumberOfLeagueGamesDrawn();
							int points = season.getPoints();
							int goalsfor = season.getNumberOfLeagueGoalsScored();
							int goalsagainst = season.getNumberOfLeagueGoalsConceded();
							int seasonid = season.getSeasonId();
							int division = season.getDivision();
							int played = season.getNumberOfLeagueGamesPlayed();
							updateLeague.setObject(1, seasonid);		
							updateLeague.setInt(2, division);
							updateLeague.setInt(3, team.getTeamDetails().getID());
							updateLeague.setInt(4, played);
							updateLeague.setInt(5, leaguewon);
							updateLeague.setInt(6, leaguelost);
							updateLeague.setInt(7, leaguedrawn);
							updateLeague.setInt(8, points);
							updateLeague.setInt(9, goalsfor);
							updateLeague.setInt(10, goalsagainst);
                                                        updateLeague.setInt(11,0);
						    updateLeague.setInt(12,j);
							updateLeague.executeUpdate();
                                                            j++;
                                                        }
						}
			             
			           
				 }catch(Exception ioe){
			           ioe.printStackTrace();
			        }
				
			}
			
			
			
		}
                *//*
    public void reversescorers()throws Exception{
            connect();
            PreparedStatement updateTeam = conn.prepareStatement("select * from matchblobs");
            int j = 1;
        ResultSet teamsResult = updateTeam.executeQuery();
        while(teamsResult.next()){
                 try{
                         String string = new String(teamsResult.getBytes("match"));
                     
                     JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
                        StringReader reader = new StringReader(string);
                        Unmarshaller marshaller=jaxbContext.createUnmarshaller();
                        
                        Match match = (Match)marshaller.unmarshal(reader);
                     
                     
                         PreparedStatement updateLeague = conn.prepareStatement("insert into scorers values(?,?,?,?,?,?,?)");
                                    int matchId = match.getID();
                                    int division = match.getDivision();
                                    int seasonid = match.getSeasonId();
                                    List scorers = match.getAwayScorer();
                                    
                                    for (int i=0;i<scorers.size();i++){
                                            
                                            Scorer scorer = (Scorer)scorers.get(i);
                                            
                                           int teamid = scorer.getTeamId();
                                           String name = scorer.getName();
                                           int goals = scorer.getGoals();
                                           
                                            updateLeague.setInt(1, matchId);            
                                            updateLeague.setString(2, name);
                                            updateLeague.setInt(3, teamid);
                                            updateLeague.setInt(4, j);
                                            updateLeague.setInt(5, goals);
                                            updateLeague.setInt(6, seasonid);
                                            updateLeague.setInt(7, division);
                                            
                                            updateLeague.executeUpdate();
                                                j++;
                                            
                                    }
                         scorers = match.getHomeScorer();
                                                             
                         for (int i=0;i<scorers.size();i++){
                                                                    
                                                                     Scorer scorer = (Scorer)scorers.get(i);
                                                                     
                                                                    int teamid = scorer.getTeamId();
                                                                    String name = scorer.getName();
                                                                    int goals = scorer.getGoals();
                                                                    
                                                                     updateLeague.setInt(1, matchId);            
                                                                     updateLeague.setString(2, name);
                                                                     updateLeague.setInt(3, teamid);
                                                                     updateLeague.setInt(4, j);
                                                                     updateLeague.setInt(5, goals);
                                                                     updateLeague.setInt(6, seasonid);
                                                                     updateLeague.setInt(7, division);
                                                                     
                                                                     updateLeague.executeUpdate();
                                                                         j++;
                                                                     
                                                             }
                         
                       
                     }catch(Exception ioe){
                       ioe.printStackTrace();
                    }
                    
            }
            
            
            
    }
    */
    public void reversematches()throws Exception{
            connect();
            PreparedStatement updateTeam = conn.prepareStatement("select * from matchblobs");
            int j = 1;
        ResultSet teamsResult = updateTeam.executeQuery();
        while(teamsResult.next()){
                 try{
                         String string = new String(teamsResult.getBytes("match"));
                     
                     JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
                        StringReader reader = new StringReader(string);
                        Unmarshaller marshaller=jaxbContext.createUnmarshaller();
                        
                        Match match = (Match)marshaller.unmarshal(reader);
                     
                     
                         PreparedStatement updateLeague = conn.prepareStatement("insert into matches values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                                    Date date = new Date(match.getDate().toGregorianCalendar().getTimeInMillis());
                                    updateLeague.setInt(1,match.getID());
                                    updateLeague.setDate(2,date);
                                    updateLeague.setInt(3,match.getHomeTeam());
                                    updateLeague.setInt(4,match.getAwayTeam());
                                    updateLeague.setInt(5,match.getHomeGoals());
                                    updateLeague.setInt(6,match.getAwayGoals());
                                    updateLeague.setInt(7,(match.isPlayed())?1:0);
                                    updateLeague.setInt(8,match.getSeasonId());
                                    updateLeague.setInt(9,match.getDivision());
                                    updateLeague.setInt(10,(match.isBlocked())?1:0);
                                    updateLeague.setInt(11,(match.isWalkover())?1:0);
                                    updateLeague.setString(12,match.getTime());
                                    updateLeague.setInt(13,0);
                                    updateLeague.setInt(14,(match.isHomeAdded())?1:0);
                                    updateLeague.setInt(15,(match.isAwayAdded())?1:0);
                                    
                         updateLeague.executeUpdate();
                                    
                         
                       
                     }catch(Exception ioe){
                       ioe.printStackTrace();
                    }
                    
            }
            
            
            
    }
    public void makeMatchesBetwixt(String number){
        connect();
        try{
        DBAccess db = new DBAccess();
		 String sql = "select * from matchblobs ";
			if(number!=null)sql= sql+ "where id="+number;
         PreparedStatement updateTeam = conn.prepareStatement(sql);
         
        
                ResultSet teamsResult = updateTeam.executeQuery();
                
                while(teamsResult.next()){
                         try{
                        String string = new String(teamsResult.getBytes("match"));
                            
                             ByteArrayInputStream input = new ByteArrayInputStream (string.getBytes());
                     JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
                        
                
                        Unmarshaller marshaller=jaxbContext.createUnmarshaller();
                        
                        Match match = (Match)marshaller.unmarshal(input);
                        
                       
                        Utils.betwixtMatch(match);
                    
                         }catch(Exception ioe){
                            ioe.printStackTrace();
                        }
                        
                }
                
       
                
        }catch(Exception e){
                e.printStackTrace();
        }
    }
            
           
	
    public void makeTeamBetwixt(String number){
        connect();
        try{
        DBAccess db = new DBAccess();
        LeagueCache.getMatchesList(db);
        String sql = "select * from teams ";
		if(number!=null)sql= sql+ "where id="+number;
		PreparedStatement updateTeam = conn.prepareStatement(sql);
       
        
               ResultSet teamsResult = updateTeam.executeQuery();

    
    
    
    while(teamsResult.next()){

                         try{

				               
                        com.bafa.objects.team.Team t = new com.bafa.objects.team.Team();
                        t.setId(teamsResult.getInt("id"));
						t.setName(teamsResult.getString("name"));
                        t.setPrimaryContactEmail(teamsResult.getString("COntactEmail1"));
                        t.setSecondaryContactEmail(teamsResult.getString("COntactEmail2"));
                        t.setPrimaryContactTel(teamsResult.getString("COntactTel1"));
                        t.setSecondaryContactTel(teamsResult.getString("COntactTel2"));
                        t.setPrimaryContactName(teamsResult.getString("COntactName1"));
                        t.setSecondaryContactName(teamsResult.getString("COntactName2"));
						t.setShirtColour(teamsResult.getString("ShirtColour"));
						t.setMoneyOwed(teamsResult.getDouble("PaymentRequired"));
                        t.setStats(new TeamStats(t.getId()));
						com.bafa.objects.schema.Team team = null;
						PreparedStatement selectblobs = conn.prepareStatement("select * from teamblobs where id=?");
						selectblobs.setInt(1,t.getId());
						ResultSet selectResult = selectblobs.executeQuery();
						while(selectResult.next()){
							String string = new String(selectResult.getBytes("team"));
                            
                             ByteArrayInputStream input = new ByteArrayInputStream (string.getBytes());
                     JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
                        
                
                        Unmarshaller marshaller=jaxbContext.createUnmarshaller();
							team = (com.bafa.objects.schema.Team)marshaller.unmarshal(input);
						}
						List seasons = team.getTeamStats().getTeamSeason();
                        for(int i=0;i<seasons.size();i++){
                            
                            TeamSeason season = (TeamSeason)seasons.get(i);
                            
                            List matches = season.getMatch();
                            
                            for(int j=0;j<matches.size();j++){
								
                                Score match = (Score)matches.get(j);
								System.out.println("JAXB MATCH ID = "+match.getID());
								com.bafa.objects.Match betwixtMatch = RetrieveServices.getMatch(match.getID());
								if(season.getSeasonId()==46 && t.getId()==13) System.out.println("MATCHES betwixt "+betwixtMatch.getID());
                                t.getStats().enterMatch(betwixtMatch,betwixtMatch.getSeasonId());
                                t.getStats().updateScores(betwixtMatch,betwixtMatch.getSeasonId());
                            }
                            
                            List scorers = season.getScorer();
                            for(int j=0;j<scorers.size();j++){
                                Scorer scorer = (Scorer)scorers.get(j);
                                t.getStats().enterScorer(scorer.getName(),season.getSeasonId());
                                
                                
                            }
                            
                        }
                        
          
                             PreparedStatement players = conn.prepareStatement("insert into teambetwixts values(?,?)");
                             players.setObject(2, Utils.writeBetwixt("team",t).getBytes());  
                             players.setInt(1,t.getId());
                       
                             players.executeUpdate();
                         }catch(Exception ioe){
                            ioe.printStackTrace();
                        }
                        
                }
                
       
                
        }catch(Exception e){
                e.printStackTrace();
        }
    }
    
    public void makeSeasonBetwixt(){
        connect();
        try{
        DBAccess db = new DBAccess();
        
            LeagueCache.getPlayers(db);
			LeagueCache.getMatchesList(db);
            GrabTeams.grabTeams(db);
         PreparedStatement updateTeam = conn.prepareStatement("select * from seasonblobs");
         
        
                ResultSet teamsResult = updateTeam.executeQuery();
                
                while(teamsResult.next()){
                         try{
                        String string = new String(teamsResult.getBytes("season"));
                            
                             ByteArrayInputStream input = new ByteArrayInputStream (string.getBytes());
                     JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
                        
                
                        Unmarshaller marshaller=jaxbContext.createUnmarshaller();
                        
                        Season season = (Season)marshaller.unmarshal(input);
                        
                        com.bafa.objects.season.Season s = new com.bafa.objects.season.Season();
                        s.setId(season.getID());
                        s.setLeague(season.isLeague());
                        s.setCompleted(season.isCompleted());
                        s.setSwitchedOn(season.isSwitchedOn());
                        s.setName(season.getSeason());
                        s.setYear(season.getYear());
                        List divisions = season.getDivision();
                        for(int i=0;i<divisions.size();i++){
                            
                            Division division = (Division)divisions.get(i);
                            
                            com.bafa.objects.season.Division d = new com.bafa.objects.season.Division();
                            d.setDivision(division.getDivision());
                            d.setSeasonId(division.getSeasonId());
                            
                            List matches = division.getMatch();
                            
                            for(int j=0;j<matches.size();j++){
                                int match = (Integer)matches.get(j);
								System.out.println("MATCH ID "+match);
                                com.bafa.objects.Match betwixtMatch = RetrieveServices.getMatch(match);
                                
                                d.enterMatch(betwixtMatch);
                                List teams = division.getTeam();
                                for(int k=0;k<teams.size();k++){
                                    Integer team = (Integer)teams.get(k);
                                    d.enterTeam(team);
                                }
                                
                                List scorers = division.getScorer();
                                for(int l=0;l<scorers.size();l++){
                                    Scorer scorer = (Scorer)scorers.get(l);
                                    d.enterScorer(scorer.getName());
                                }
                                
                                
                                
                               
                            }
                            
							 s.enterDivision(d);
                             
                        }
                        
          
                             PreparedStatement players = db.conn.prepareStatement("insert into seasonbetwixt values(?,?)");
                             players.setObject(2, Utils.writeBetwixt("season",s).getBytes());  
                             players.setInt(1,s.getId());
                       
                             players.executeUpdate();
                         }catch(Exception ioe){
                            ioe.printStackTrace();
                        }
                        
                }
                
       
                
        }catch(Exception e){
                e.printStackTrace();
        }
    }
    
    
    
    public void makePlayerBetwixts(String like){
        connect();
        try{
        DBAccess db = new DBAccess();
		 PreparedStatement updateTeam = null;
		if(like!=null)
			updateTeam = conn.prepareStatement("select * from scorers where scorername like '"+like+"%'");
		else
			updateTeam = conn.prepareStatement("select * from scorers where scorername");
		
		PreparedStatement seasons = conn.prepareStatement("select * from season where id=?");
        
                ResultSet teamsResult = updateTeam.executeQuery();
                HashMap addedPlayers = new HashMap();
                int i=0;
                while(teamsResult.next()){
                         try{
                             Player player = new Player();
                             boolean added = false;
                        if(addedPlayers.containsKey(teamsResult.getString("scorername").toUpperCase())){
                           added = true;
                            player = (Player)addedPlayers.get(teamsResult.getString("scorername").toUpperCase());
                        }
                        player.setFirstname(teamsResult.getString("scorername"));
                        player.setTotalGoals(player.getTotalGoals()+teamsResult.getInt("numberofgoals"));
                        seasons.setInt(1,teamsResult.getInt("seasonId"));
                        ResultSet seasonQuery = seasons.executeQuery();
                        boolean isLeague = false;
                        while(seasonQuery.next()){
                            if(seasonQuery.getString("CupLeague").equalsIgnoreCase("League")) isLeague = true;
                        }
                        PlayerSeason season = new PlayerSeason();
                        PlayerTeam team = new PlayerTeam();
						PlayerTeam seasonTeam = new PlayerTeam();
                        PlayerDivision division = new PlayerDivision();
						PlayerDivision seasonDivision = new PlayerDivision();
						PlayerMatch match = new PlayerMatch();
						match.setMatch(teamsResult.getInt("matchId"));
						match.setGoals(teamsResult.getInt("numberofgoals"));
						match.setTeamId(teamsResult.getInt("teamId"));
						
						if(player.getSeasonGoals()==null)player.setSeasonGoals(new TreeMap());
                        if(player.getTeamGoals()==null)player.setTeamGoals(new TreeMap());
                        if(player.getDivisionGoals()==null)player.setDivisionGoals(new TreeMap());
                        if(player.getSeasonGoals().containsKey(teamsResult.getInt("seasonId"))){
                            season = (PlayerSeason)player.getSeasonGoals().get(teamsResult.getInt("seasonId"));
                        }
                        if(player.getTeamGoals().containsKey(teamsResult.getInt("teamId"))){
                            team = (PlayerTeam)player.getTeamGoals().get(teamsResult.getInt("teamId"));
                        }
                        
                        if(player.getDivisionGoals().containsKey(teamsResult.getInt("divisionId"))){
                            division = (PlayerDivision)player.getDivisionGoals().get(teamsResult.getInt("divisionId"));
                        }  
                        if(season.getDivisionGoals().containsKey(teamsResult.getInt("divisionId"))){
							seasonDivision = (PlayerDivision)season.getDivisionGoals().get(teamsResult.getInt("divisionId"));
                        }
						if(season.getTeamGoals().containsKey(teamsResult.getInt("teamId"))){
							seasonTeam = (PlayerTeam)season.getTeamGoals().get(teamsResult.getInt("teamId"));
                        }
						seasonTeam.setTeamId(teamsResult.getInt("teamId"));
						seasonTeam.setGoals(seasonTeam.getGoals()+teamsResult.getInt("numberofgoals"));
						seasonDivision.setDivision(teamsResult.getInt("divisionId"));
						seasonDivision.setGoals(seasonDivision.getGoals()+teamsResult.getInt("numberofgoals"));
						season.setGoals(season.getGoals()+teamsResult.getInt("numberofgoals"));
						
                        team.setGoals(team.getGoals()+teamsResult.getInt("numberofgoals"));
                        division.setGoals(division.getGoals()+teamsResult.getInt("numberofgoals"));
                        season.setSeasonId(teamsResult.getInt("seasonId"));
                        team.setTeamId(teamsResult.getInt("teamId"));
                        division.setDivision(teamsResult.getInt("divisionId"));
                             
                        player.getSeasonGoals().remove(teamsResult.getInt("seasonId"));
                        player.addSeasonGoals(teamsResult.getInt("seasonId"),season);
                        player.getTeamGoals().remove(teamsResult.getInt("teamId"));
                        player.addTeamGoals(teamsResult.getInt("teamId"),team);
						season.getDivisionGoals().remove(teamsResult.getInt("divisionId"));
                        season.addDivisionGoals(teamsResult.getInt("divisionId"),seasonDivision);
						season.getTeamGoals().remove(teamsResult.getInt("teamId"));
						season.addTeamGoals(teamsResult.getInt("teamId"),seasonTeam);
						player.addMatchGoals(match.getMatch(),match);
                        if(isLeague){
                            player.getDivisionGoals().remove(teamsResult.getInt("divisionId"));
                            player.addDivisionGoals(teamsResult.getInt("divisionId"),division);
                        }
                        addedPlayers.put(teamsResult.getString("scorername").toUpperCase(),player);
						
						if(added){
                            PreparedStatement players = db.conn.prepareStatement("update players set player=? where id=?");
                            players.setObject(1, Utils.writeBetwixt("player",player).getBytes());  
                            players.setString(2,player.getFirstname().toUpperCase());
                            players.executeUpdate();
                        }else{
                            player.setId(i);
                        PreparedStatement players = db.conn.prepareStatement("insert into players values(?,?)");
                        players.setObject(2, Utils.writeBetwixt("player",player).getBytes());  
                        players.setString(1,player.getFirstname().toUpperCase());
                        i++;
                        players.executeUpdate();
                        }
                         }catch(Exception ioe){
                            ioe.printStackTrace();
                        }
                        
                }
                
       
                
        }catch(Exception e){
                e.printStackTrace();
        }
    }
    
	
	public void updatePlayerBetwixts(){
		
		try{
			LeagueCache.getPlayers(new DBAccess());
			LeagueCache.getMatchesList(new DBAccess());
			Iterator itr = LeagueCache.getPlayers().values().iterator();
			while(itr.hasNext()){
				Player player = (Player)itr.next();
				player.getMatchGoals().values().iterator();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
    public void maketeamblobs(String number){
        QueryObject teamsQuery = new QueryObject("teams");
        
        connect();
        try{
        DBAccess db = new DBAccess();
		String sql = "select * from teams ";
		if(number!=null)sql= sql+ "where id="+number;
            PreparedStatement updateTeam = conn.prepareStatement(sql);
           
            
                   ResultSet teamsResult = updateTeam.executeQuery();

        
        
        
        while(teamsResult.next()){
                int teamId = teamsResult.getInt("ID");
                ObjectFactory factory=new ObjectFactory();
                com.bafa.objects.schema.Team team = factory.createTeam();
                TeamDetails teamDetails = factory.createTeamDetails();
                teamDetails.setID(teamId);
                teamDetails.setName(teamsResult.getString("Name"));
                teamDetails.setContactName1(teamsResult.getString("COntactName1"));
                teamDetails.setContactName2(teamsResult.getString("COntactName2"));
                teamDetails.setContactEmail1(teamsResult.getString("COntactEmail1"));
                teamDetails.setContactEmail2(teamsResult.getString("COntactEmail2"));
                teamDetails.setContactTel1(teamsResult.getString("COntactTel1"));
                teamDetails.setContactTel2(teamsResult.getString("COntactTel2"));
                teamDetails.setHomeShirtColour(teamsResult.getString("ShirtColour"));
                teamDetails.setPaymentRequired(teamsResult.getDouble("PaymentRequired"));
        
                team.setTeamDetails(teamDetails);
                // Get Stats
                GrabTeamStats.grabTeamStats(db,team);
				JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
				StringWriter writer = new StringWriter();
				Marshaller marshaller=jaxbContext.createMarshaller();
				
				marshaller.marshal(team, writer);
					       
                
				PreparedStatement players = conn.prepareStatement("insert into teamblobs values(?,?)");
				players.setObject(2,writer.toString().getBytes());  
                players.setInt(1,team.getTeamDetails().getID());
          
                players.executeUpdate();
        }
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    
    public void makeSeasonsBlobs(){
        connect();
        try{
        DBAccess db = new DBAccess();
       
        ObjectFactory factory = new ObjectFactory();
        QueryObject seasonsQuery = new QueryObject("season");
		
        seasonsQuery.addOrderBy("year",true);
        seasonsQuery.addOrderBy("id",true);
        
        
        ResultSet seasonsResults = db.query(seasonsQuery);
        int i = 0;
        while(seasonsResults.next()){
                Season season = factory.createSeason();
                season.setID(seasonsResults.getInt("id"));
                season.setSeason(seasonsResults.getString("season"));
                season.setYear(seasonsResults.getInt("year"));
                season.setLeague((seasonsResults.getString("cupleague").equalsIgnoreCase("league")));
                
                season.setSwitchedOn((seasonsResults.getInt("switchon")==1));
               
                
                        grabIsSeasonComplete(db,season);
                        GrabDivision.grabDivision(db,season);
                        if(!season.isLeague()){
                                grabCupRounds(db,season);
                                grabPlateRounds(db,season);
                        }
						JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
						StringWriter writer = new StringWriter();
						Marshaller marshaller=jaxbContext.createMarshaller();
						
						marshaller.marshal(season, writer);
						
						PreparedStatement players = conn.prepareStatement("insert into seasonblobs values(?,?)");
						players.setObject(2,writer.toString().getBytes());  
		                players.setInt(1,season.getID());
		          
		                players.executeUpdate();
                i++;
        }
        }catch(Exception e){
            
        }
    }
    private static void grabIsSeasonComplete(DBAccess db, Season season)throws Exception{
            QueryObject seasonCompletedQuery = new QueryObject("matches");
            seasonCompletedQuery.addWhereAndField("SeasonId",""+season.getID());
            seasonCompletedQuery.addWhereAndField("Played","1");
            
            ResultSet seasonCompletedResults = db.count(seasonCompletedQuery);
            
            QueryObject seasonTotalQuery = new QueryObject("matches");
            seasonTotalQuery.addWhereAndField("SeasonId",""+season.getID());
            
            ResultSet seasonTotalResults = db.count(seasonTotalQuery);
            
            while(seasonCompletedResults.next()&&seasonTotalResults.next()){
                    if(seasonCompletedResults.getInt(1)==seasonTotalResults.getInt(1))
                            season.setCompleted(true);
            }
    }
    
    public static void grabCupRounds(DBAccess db, Season season)throws Exception{
            grabRounds(db,season,true);
    }
   
    public static void grabPlateRounds(DBAccess db, Season season)throws Exception{
            grabRounds(db,season,false);
    }
    
    private static void grabRounds(DBAccess db, Season season, boolean cup)throws Exception{
          /*  ObjectFactory factory = new ObjectFactory();
            // get all cup rounds for season        
            QueryObject queryRounds = new QueryObject("rounds");
            queryRounds.addWhereAndField("SeasonId",""+season.getID());
            if(cup)queryRounds.addWhereAndField("cup","1");
            else queryRounds.addWhereAndField("cup","0");
            queryRounds.addOrderBy("previous",false);
            
            
            Round round = null;
            try{
                    ResultSet resultRounds = db.query(queryRounds);
                    
                    // BEGIN GET FIRST ROUND
                    if(resultRounds.next()){
                            // set up the round
                            round = factory.createRound(); 
                            round.setName(resultRounds.getString("Name"));
                            round.setCup(resultRounds.getInt("Cup")>0);
                            round.setID(resultRounds.getInt("ID"));
                            round.setPreviousRound(resultRounds.getInt("Previous"));
                            round.setConfirmed(resultRounds.getInt("Confirmed")>0);
                            QueryObject queryRoundFormat = new QueryObject("roundsformat");
                            queryRoundFormat.addWhereAndField("roundId",""+round.getID());
                            
                            ResultSet formatResult = db.query(queryRoundFormat);
                            
                            // now get all the matches for that round
                            while(formatResult.next()){
                                    int groupA = formatResult.getInt("A");
                                    int groupB = formatResult.getInt("B");
                                    int posA = formatResult.getInt("posA");
                                    int posB = formatResult.getInt("posB");
                                    int bye = formatResult.getInt("bye");
                                    // are the respective groups complete?
                                    boolean groupAComplete = GrabDivision.isDivisionComplete(db,season.getID(),groupA);
                                    boolean groupBComplete = GrabDivision.isDivisionComplete(db,season.getID(),groupB);
                                    
                                    
                                    
                                    // now reset team names in match
                                    Match match = RetrieveCache.getMatch(formatResult.getInt("matchid"));
                                    if(groupAComplete){
                                            match.setHomeTeamName(GrabDivision.grabTeamInPos(season.getID(),db,groupA,posA));
                                            SeasonServices.confirmTeamForRound(match.getID(), season.getID(), GrabDivision.grabTeamIdInPos(season.getID(),db,groupA,posA), 0,false);
                                    }
                                    else{
                                            if(posA==1){
                                                    match.setHomeTeamName("Winner Group "+groupA);
                                            }else if(posA==2){
                                                    match.setHomeTeamName("Runner-up Group "+groupA);
                                            }else{
                                                    match.setHomeTeamName("Position "+posA+" in Group "+groupA);
                                            }
                                    }
                                    if(bye>0){
                                            match.setAwayTeamName("BYE");
                                    }else if(groupBComplete){
                                            match.setAwayTeamName(GrabDivision.grabTeamInPos(season.getID(),db,groupB,posB));
                                            SeasonServices.confirmTeamForRound(match.getID(), season.getID(), 0, GrabDivision.grabTeamIdInPos(season.getID(),db,groupB,posB),false);
                                    }
                                    else{
                                            if(posB==1){
                                                    match.setAwayTeamName("Winner Group "+groupB);
                                            }else if(posB==2){
                                                    match.setAwayTeamName("Runner-up Group "+groupB);
                                            }else{
                                                    match.setAwayTeamName("Position "+posB+" in Group "+groupB);
                                            }
                                    }
                                    
                                    // if both groups are done then confirm the round
                                    if(groupAComplete&&groupBComplete){
                                            SeasonServices.confirmRound(match.getID(),season.getID(),GrabDivision.grabTeamIdInPos(season.getID(),db,groupA,posA),GrabDivision.grabTeamIdInPos(season.getID(),db,groupB,posB),false);
                                    }
                                    round.getMatches().add(match.getID());
                            }
                            
                            if(round.isCup()){
                                    season.getCupRound().add(round);
                            }else{
                                    season.getPlateRound().add(round);
                            }
                            
                    }
                    // END GET FIRST ROUND
                    
                    //BEGIN GET ALL OTHER ROUNDS
                    while(resultRounds.next()){
                            round = factory.createRound(); 
                            round.setName(resultRounds.getString("Name"));
                            round.setCup(resultRounds.getInt("Cup")>0);
                            round.setID(resultRounds.getInt("ID"));
                            round.setPreviousRound(resultRounds.getInt("Previous"));
                            round.setConfirmed(resultRounds.getInt("Confirmed")>0);
                            QueryObject queryRoundFormat = new QueryObject("roundsformat");
                            queryRoundFormat.addWhereAndField("roundId",""+round.getID());
                            
                            ResultSet formatResult = db.query(queryRoundFormat);
                            
                            // now get all the matches for that round
                            while(formatResult.next()){
                                    int groupA = formatResult.getInt("A");
                                    int groupB = formatResult.getInt("B");
                                    int bye = formatResult.getInt("bye");
                                    // are the respective groups complete?
                                    boolean groupAComplete = RetrieveCache.getMatch(groupA).isPlayed();
                                    boolean groupBComplete = RetrieveCache.getMatch(groupB).isPlayed();
                                            
                                    // now reset team names in match
                                    Match match = RetrieveCache.getMatch(formatResult.getInt("matchid"));
                                    Match matchA = RetrieveCache.getMatch(groupA);
                                    if(groupAComplete){
                                            
                                            int team = 0;
                                            if(matchA.getHomeGoals()>matchA.getAwayGoals())
                                                    team = matchA.getHomeTeam();
                                            else if(matchA.getHomeGoals()<matchA.getAwayGoals())
                                                    team = matchA.getAwayTeam();
                                            match.setHomeTeam(team);
                                            SeasonServices.confirmTeamForRound(match.getID(), season.getID(), team, 0,false);
                                    }
                                    else{
                                            
                                            match.setHomeTeamName(matchA.getHomeTeamName()+"/"+matchA.getAwayTeamName());
                                    }
                                    Match matchB = RetrieveCache.getMatch(groupB);
                                    if(bye>0){
                                            match.setAwayTeamName("BYE");
                                    }else if(groupBComplete){
                                            
                                            int team = 0;
                                            if(matchB.getHomeGoals()>matchB.getAwayGoals())
                                                    team = matchB.getHomeTeam();
                                            else if(matchB.getHomeGoals()<matchB.getAwayGoals())
                                                    team = matchB.getAwayTeam();
                                            match.setAwayTeam(team);
                                            SeasonServices.confirmTeamForRound(match.getID(), season.getID(), 0,team,false);
                                    }
                                    else{
                                            match.setAwayTeamName(matchB.getHomeTeamName()+"/"+matchB.getAwayTeamName());
                                    }
                                    
                                    round.getMatches().add(match.getID());
                                  
                                    //if both groups are done then confirm the round
                                    if(groupAComplete&&groupBComplete){
                                            SeasonServices.confirmRound(match.getID(),season.getID(),match.getHomeTeam(),match.getAwayTeam(),false);
                                    }
                            }
                            
                            if(round.isCup()){
                                    season.getCupRound().add(round);
                            }else{
                                    season.getPlateRound().add(round);
                            }
                    }
                    // END GET ALL OTHER ROUNDS
                    
                    
            }catch(Exception e){
                    e.printStackTrace();
            }*/
    }
    
    public void makeMatchBlobs(String number){
		DBAccess db = new DBAccess();
		try{
			ObjectFactory factory = new ObjectFactory();
			
			QueryObject query3 = new QueryObject("matches");
			if(number!=null)query3.addWhereAndField("id",number);
			ResultSet rs4 = db.query(query3);
			while(rs4.next()){
				Match match = factory.createMatch();
				XMLGregorianCalendar cal =DatatypeFactory.newInstance().newXMLGregorianCalendar();
				
				if(rs4.getDate(2)!=null){
					cal.setYear(rs4.getDate(2).getYear()+1900);
					cal.setMonth(rs4.getDate(2).getMonth()+1);
					cal.setDay(rs4.getDate(2).getDate());
					match.setDate(cal);
				}
				match.setID(rs4.getInt("ID"));
				match.setAwayAdded((rs4.getInt("AddedB")==1));
				match.setHomeAdded((rs4.getInt("AddedA")==1));
				match.setAwayGoals(rs4.getInt("GoalsB"));
				match.setHomeGoals(rs4.getInt("GoalsA"));
				match.setAwayTeam(rs4.getInt("TeamB"));
				match.setHomeTeam(rs4.getInt("TeamA"));
				match.setBlocked((rs4.getInt("Blocked"))==1);
				match.setTime(rs4.getString("Time"));
				if(match.isAwayAdded()&&match.isHomeAdded())match.setConfirmed(true);
				
				match.setDivision(rs4.getInt("Division"));
				match.setSeasonId(rs4.getInt("SeasonId"));
				match.setPlayed((rs4.getInt("Played"))==1);
				addScorersToMatch(match,db);
				JAXBContext jaxbContext=JAXBContext.newInstance("com.bafa.objects.schema");
				StringWriter writer = new StringWriter();
				Marshaller marshaller=jaxbContext.createMarshaller();
				
				marshaller.marshal(match, writer);
				
				PreparedStatement players = db.conn.prepareStatement("insert into matchblobs values(?,?)");
				players.setObject(2,writer.toString().getBytes());  
                players.setInt(1,match.getID());
          
                players.executeUpdate();
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}
    }
    public void addScorersToMatch(Match match, DBAccess db){
	try{
		ObjectFactory factory = new ObjectFactory();
	
	
	QueryObject query3 = new QueryObject("scorers");
	query3.addWhereAndField("matchId",new Integer(match.getID()).toString());
	ResultSet rs3 = db.query(query3);
	
	
	while(rs3.next()){
		Scorer scorer = factory.createScorer();
		scorer.setGoals(rs3.getInt("NumberOfGoals"));
		scorer.setName(rs3.getString("scorername"));
		scorer.setTeamId(rs3.getInt("teamId"));
		if(rs3.getInt("teamId")==match.getHomeTeam()){
			match.getHomeScorer().add(scorer);
		}else{
			match.getAwayScorer().add(scorer);
		}
	
	}
	}catch(Exception e){
		e.printStackTrace();
	}
    }
	
		public static void main(String[] args) {
			ReverseEngineerData reverse = new ReverseEngineerData();
			try{
				
				//GrabTeams.grabTeams(new DBAccess());
			    //reverse.reversematches();
			    //reverse.reverseleague();
                            //reverse.reversescorers();
				//reverse.makeMatchBlobs();
				//reverse.makeMatchesBetwixt();
                           reverse.makePlayerBetwixts(null);
				
                           //reverse.maketeamblobs();
                          //reverse.makeTeamBetwixt(null);
				//reverse.makeSeasonsBlobs();           
				//reverse.makeSeasonBetwixt();
                           
				System.out.println("ALL DONE");
			}catch(Exception e){
				e.printStackTrace();
			}
		}
}
